﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWDataDB.DAL;
using SWDataDB.Models;

namespace SWDataDB.DAL
{
    public class YXDeepthInfo
    {
        public YXDeepthInfo() { }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strExp"></param>
        /// <returns></returns>
        public DataSet GetInfoForPageByASC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.YX_DEEPTH_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID ASC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strExp"></param>
        /// <returns></returns>
        public DataSet GetInfoForPageByDESC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.YX_DEEPTH_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID Asc";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


        //获得所有的钻孔描述信息
        public DataSet SelectZKDes()
        {
            string strSql = "SELECT * FROM dbo.YX_DEEPTH_TBL";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


        public DataSet SelectByZKCode(string zk_code)
        {
            string strSql = "SELECT * FROM dbo.YX_DEEPTH_TBL WHERE ZK_CODE ='";
            strSql += zk_code + "'";

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        //通过ZK_CODE和ZL_FILE_ID获得对应的钻孔深度记录集
        public DataSet SelectByZKCodeAndFID(string zk_code,string zl_file_id)
        {
            string strSql = "SELECT * FROM dbo.YX_DEEPTH_TBL WHERE ZK_CODE ='";
            strSql += zk_code + "'and ZL_FILE_ID='";
            strSql += zl_file_id + "'";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        public DataSet SelectByZKID(string zkid)
        {
            string strSql = "SELECT * FROM dbo.YX_DEEPTH_TBL WHERE ZK_CODE='";
            strSql += zkid + "'";

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        public DataSet SelectInfoByFileID(string zl_file_id)
        {
            string strSql = "SELECT * FROM dbo.YX_DEEPTH_TBL WHERE ZL_FILE_ID ='";
            strSql += zl_file_id + "'";

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


        public DataSet SelectInfoByID(int id)
        {
            string strSql = "SELECT * FROM dbo.YX_DEEPTH_TBL WHERE ID =";
            strSql += id;

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="yx_info"></param>
        /// <returns></returns>
        public int AddYXDeepthInfo(YX_DEEPTH_TBL yx_info)
        {
            // 先处理普通数据
            string strSql = "INSERT INTO dbo.YX_DEEPTH_TBL(ZL_FILE_ID,ZK_CODE,CY_DEEP,YX_DESRIBE) VALUES(";
            strSql += "'" + yx_info.O_ZL_FILE_ID + "',";
            strSql += "'" + yx_info.O_ZK_CODE + "',";
            strSql += "'" + yx_info.O_CY_DEEP + "',";
            strSql += "'" + yx_info.O_YX_DESRIBE + "')";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteYXDeepthById(int id)
        {
            string strSql = "DELETE FROM dbo.YX_DEEPTH_TBL WHERE ID =";
            strSql += id.ToString();

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="yx_info"></param>
        /// <returns></returns>
        public int UpdateYXInfo(YX_DEEPTH_TBL yx_info)
        {
            string strSql = "UPDATE dbo.YX_DEEPTH_TBL ";
            strSql += "SET ZK_CODE='" + yx_info.O_ZK_CODE + "',";
            strSql += "ZL_FILE_ID='" + yx_info.O_ZL_FILE_ID + "',";
            strSql += "CY_DEEP='" + yx_info.O_CY_DEEP + "',";
            strSql += "YX_DESRIBE='" + yx_info.O_YX_DESRIBE + "'";

            strSql += " WHERE ID=" + yx_info.O_ID;

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }
    }
}
